package com.xoa.service.hr.impl;

import com.alibaba.fastjson.JSONArray;
import com.xoa.dao.common.SysCodeMapper;
import com.xoa.dao.department.DepartmentMapper;
import com.xoa.dao.enclosure.AttachmentMapper;
import com.xoa.dao.hr.*;
import com.xoa.dao.users.OrgManageMapper;
import com.xoa.dao.users.UserExtMapper;
import com.xoa.dao.users.UsersMapper;
import com.xoa.model.common.SysCode;
import com.xoa.model.department.Department;
import com.xoa.model.email.EmailBodyModel;
import com.xoa.model.email.EmailModel;
import com.xoa.model.email.Webmail;
import com.xoa.model.enclosure.Attachment;
import com.xoa.model.hr.*;
import com.xoa.model.modulePriv.ModulePriv;
import com.xoa.model.sms.SmsBody;
import com.xoa.model.users.OrgManage;
import com.xoa.model.users.UserExt;
import com.xoa.model.users.Users;
import com.xoa.service.email.EmailService;
import com.xoa.service.hr.HrStaffEntryService;
import com.xoa.service.sms.SmsService;
import com.xoa.service.users.UsersService;
import com.xoa.util.*;
import com.xoa.util.common.StringUtils;
import com.xoa.util.common.log.FileUtils;
import com.xoa.util.common.session.SessionUtils;
import com.xoa.util.dataSource.ContextHolder;
import com.xoa.util.encrypt.EncryptSalt;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.scheduling.annotation.Async;
import org.springframework.scheduling.concurrent.ThreadPoolTaskExecutor;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;

import javax.annotation.Resource;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.Cookie;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.util.*;

@Service
public class HrStaffServiceImpl implements HrStaffEntryService {
    @Resource
    private HrStaffEntryMapper hrStaffEntryMapper;
    @Resource
    private HrStaffInfoMapper hrStaffInfoMapper;
    @Autowired
    private UsersService usersService;
    @Autowired
    private EmailService emailService;
    @Resource
    private DepartmentMapper departmentMapper;
    @Resource
    private UsersMapper usersMapper;
    @Resource
    private UserExtMapper userExtMapper;
    @Resource
    ThreadPoolTaskExecutor threadPoolTaskExecutor;
    @Resource
    private HrStaffEntryEducationMapper hrStaffEntryEducationMapper;

    @Resource
    private SysCodeMapper sysCodeMapper;
    @Resource
    private SmsService smsService;
    @Resource
    private HrStaffEntryExperienceMapper hrStaffEntryExperienceMapper;

    @Resource
    private HrStaffAttachmentMapper hrStaffAttachmentMapper;

    @Resource
    private AttachmentMapper attachmentMapper;

    @Resource
    private OrgManageMapper orgManageMapper;

    @Override
    @Transactional(rollbackFor = RuntimeException.class)
    public ToJson updateStateById(HttpServletRequest request, String auditState, Integer id, String msg) {
        ToJson json=new ToJson(1,"服务异常");
        try {

            Cookie redisSessionCookie = CookiesUtil.getCookieByName(request, "redisSessionId");
            Users nowUser = SessionUtils.getSessionInfo(request.getSession(), Users.class, new Users(),redisSessionCookie);

            HrStaffEntry entry1 = hrStaffEntryMapper.selectByPrimaryKey(id);

            if(auditState!=null && !auditState.equals("")) {
                if (!"152".equals(nowUser.getUserId())) {
                    Map<String,Object> map = new HashMap<String,Object>();
                    map.put("deptId", nowUser.getDeptId());
                    switch (entry1.getAuditState()) {
                        // 第二步，采销转交给部门经理
                        case "1":
                            map.put("userPriv", 4); // 部门经理userPriv
                            break;
                        // 第三步，部门经理转交给部门助理
                        case "2":
                            map.put("userPriv", 5); // 部门助理userPriv
                            break;
                        // 第四步，部门助理转交给合同组管理者
                        case "3":
                            map.put("userPriv", 10); // 合同审核管理员的userPriv
                            map.remove("deptId");
                            break;
                        // 第五步，合同组转交给HRBP
                        case "4":
                            map.put("userPriv", 2); // 人事管理员的userPriv
                            map.remove("deptId");
                            break;
                        // 第六步，HRBP转交给商企赋能审核管理者
                        case "5":
                            map.put("userId", 152); //审核管理员的userID
                            map.remove("deptId");
                            break;
                    }
                    List<Users> users2 =  usersMapper.getUserByUserPrivAnddeptId(map);
                    if(users2.size()<1){
                        // 如果为空的话 判断是不是第二步 部门经理转交部门助理 如果是的话 就取找部门中设置的部门助理 如果没有的话 就去上一级部门去寻找部门助理
                        if("2".equals(entry1.getAuditState())){
                            Department deptById = departmentMapper.getDeptById(nowUser.getDeptId());
                            if (!StringUtils.checkNull(deptById.getAssistantId())) {
                                List<Users> userByUserIds = usersMapper.getUserByUserIds(deptById.getAssistantId().split(","));
                                if (userByUserIds.size() > 0) {
                                    users2.addAll(userByUserIds);
                                } else {
                                    Integer deptParent = deptById.getDeptParent();
                                    if (deptParent != null && deptParent != 0) {
                                        map.put("deptId", deptParent);
                                        List<Users> userByUserPrivAnddeptId = usersMapper.getUserByUserPrivAnddeptId(map);
                                        if (userByUserPrivAnddeptId.size() > 0) {
                                            users2.addAll(userByUserPrivAnddeptId);
                                        } else {
                                            Department deptById1 = departmentMapper.getDeptById(deptParent);
                                            if (!StringUtils.checkNull(deptById1.getAssistantId())) {
                                                List<Users> userByUserIds2 = usersMapper.getUserByUserIds(deptById1.getAssistantId().split(","));
                                                if (userByUserIds2.size() > 0) {
                                                    users2.addAll(userByUserIds2);
                                                }
                                            }
                                        }
                                    }
                                }
                            }
                        }
                    }
                    if(users2.size()>0){
                        StringBuilder toIds = new StringBuilder();
                        for (int i = 0; i <users2.size() ; i++) {
                            toIds.append(users2.get(i).getUserId()).append(",");
                        }
                        addAffairs(entry1, entry1.getId(), toIds.toString(), request);
                        entry1.setAuditState(auditState);
                        entry1.setWorkStatus("01");
                        hrStaffEntryMapper.updateByPrimaryKeySelective(entry1);
                        json.setFlag(0);
                        json.setMsg("审核成功");
                        return json;
                    }
                    else{
                        json.setFlag(1);
                        json.setMsg("下一步骤没有对应的审核人员，审核失败");
                        return json;
                    }
                }

                boolean contains = auditState.contains("8");
                if(auditState.equals("7")|| contains) {
                    ToJson<Webmail> json11 = emailService.selectUserWebMail("admin");
                    EmailBodyModel emailBodyModel = new EmailBodyModel();
                    emailBodyModel.setSubject("京东驻场系统注册审核");
                    Date date = new Date();

                    emailBodyModel.setToWebmail(entry1.getStaffEmail());
                    emailBodyModel.setToId2(entry1.getUserId() + ",");
                    emailBodyModel.setFromWebmail(json11.getObj().get(0).getEmailUser());
                    if (auditState.equals("7")) {
                        emailBodyModel.setContent("审核成功，初始登录账号为" + entry1.getStaffPhone() + "初始登录密码为abc123");
                    } else if (contains) {
                        emailBodyModel.setContent("审核未通过,申请已被" + nowUser.getUserPrivName()+"退回，退回原因是"+msg);
                    }

                    String sqlType = "xoa" + (String) request.getSession().getAttribute(
                            "loginDateSouse");
                    ContextHolder.setConsumerType(sqlType);

                    String toID = emailBodyModel.getToId2().trim()
                            + emailBodyModel.getCopyToId().trim()
                            + emailBodyModel.getSecretToId().trim();

                    Cookie redisSessionId1 = CookiesUtil.getCookieByName(request, "redisSessionId");
                    Users user = SessionUtils.getSessionInfo(request.getSession(), Users.class, new Users(), redisSessionId1);
                    UserExt userExt1 = userExtMapper.selUserExtByUserId(user.getUserId());

                    String emailRecentLinkman = userExt1.getEmailRecentLinkman();
                    String sendFlag = null;
                    String remind = "1";

                    if (!toID.equals("")) {
                        if (!toID.substring(toID.length() - 1).equals(",")) {
                            toID += ",";
                        }
                    }
                    emailRecentLinkman = toID + emailRecentLinkman;
                    if (emailRecentLinkman.length() > 200) {
                        emailRecentLinkman = emailRecentLinkman.substring(0, 200);
                    }
                    userExt1.setEmailRecentLinkman(emailRecentLinkman);
                    userExtMapper.updateUserExtByUid(userExt1);

                    if (StringUtils.checkNull(emailBodyModel.getFromId())) {
                        Cookie redisSessionId2 = CookiesUtil.getCookieByName(request, "redisSessionId");
                        String userId1 = SessionUtils.getSessionInfo(request.getSession(), Users.class, new Users(), redisSessionId2).getUserId();
                        emailBodyModel.setFromId(userId1);
                    }

                    if ("0".equals(sendFlag)) {
                        emailBodyModel.setSendFlag("1");
                        emailService.draftsSendEmail(emailBodyModel, new EmailModel(), sqlType, request);
                    } else {
                        emailService.sendEmail(emailBodyModel, new EmailModel(), sqlType, remind, request);
                    }


                    if(auditState.equals("7")) {
                        Cookie redisSessionId = CookiesUtil.getCookieByName(request, "redisSessionId");
                        String userId = SessionUtils.getSessionInfo(request.getSession(), Users.class, new Users(), redisSessionId).getUserId();
                        //  entry.setDeptId(Integer.parseInt(deptId.substring(0,deptId.length()-1)));
                        hrStaffInfoMapper.insertByEntry(entry1);
                        int ids = entry1.getId();


                        //  int did=entry1.getId();
                        HrStaffInfo hrStaffInfo1 = hrStaffInfoMapper.selectByLimit();

                        //修改设置档案id eid
                        hrStaffEntryEducationMapper.updateByEntryId(hrStaffInfo1.getStaffId(),id);
                        hrStaffEntryExperienceMapper.updateByEntryId(hrStaffInfo1.getStaffId(),id);
                        HrStaffInfo hrStaffInfo = hrStaffInfoMapper.selectByPrimaryKey(ids);
//eid档案  did入职
                        //  HrStaffInfo hrStaffInfo = hrStaffInfoMapper.selectByTel(entry.getStaffPhone());
                        hrStaffInfo.setCreateUserId(userId);
                        //  hrStaffInfo.setCreateDeptId(usersMapper.findUsersByuserId(userId).getDeptId());
                        hrStaffInfo.setCreateDeptId(SessionUtils.getSessionInfo(request.getSession(), Users.class, new Users(), redisSessionId).getDeptId());
                        Users users = new Users();
                        users.setUserName(entry1.getStaffName());
                        //     users.setDeptId(Integer.parseInt(deptId.substring(0, deptId.length() - 1)));
                      //  users.setDeptId(deptId);
                        users.setIdNumber(entry1.getStaffCardNo());
                        users.setPost(entry1.getWorkJob());
                        users.setMobilNo(entry1.getStaffPhone());
                        users.setBirthday(entry1.getStaffBirth());
                        users.setSex(entry1.getStaffSex());
                        users.setByname(entry1.getStaffPhone());
                        users.setUserId(entry1.getUserId());
                        UserExt userExt = new UserExt();
                        ModulePriv modulePriv = new ModulePriv();
                        String password = "abc123";
                        users.setPassword(password);
                        users.setUserPriv(7);
                        users.setNotLogin((byte) 0);
                        users.setDeptId(entry1.getDeptId());
                        users.setTheme((byte) 6);
                        Users usersByname = usersMapper.getUsersByname(users.getByname());
                        if(usersByname!=null){
                            json.setCode("0");
                            json.setFlag(0);
                            json.setMsg("已经有重复的手机号码");
                        }
                        ToJson<Users> json1 = usersService.addUser(users, userExt, modulePriv, request);
                        Users users2 = (Users) json1.getObject();
                        entry1.setUserId(users2.getUserId());
                        entry1.setId(id);
                        hrStaffInfo.setUserId(users2.getUserId());

                        hrStaffEntryMapper.updateByPrimaryKeySelective(entry1);
                        hrStaffInfo.setStaffId(ids);
                        hrStaffInfoMapper.updateByPrimaryKeySelective(hrStaffInfo);
                        // 更新附件信息userId绑定
                        hrStaffAttachmentMapper.updateUserIdByEntryId(String.valueOf(id),users2.getUserId());

                    }
                    int count = hrStaffEntryMapper.updateStateByUserId(auditState, id, date);
                }
                entry1.setAuditState(auditState);
                entry1.setWorkStatus("01");
                hrStaffEntryMapper.updateByPrimaryKeySelective(entry1);
            }

            json.setCode("0");
            json.setFlag(0);
            json.setMsg("操作成功");
        }
        catch (Exception e){
            e.printStackTrace();
            json.setCode("1");
            json.setObj1(e);
            json.setMsg("服务异常");
            json.setFlag(1);

        }
        return  json;
    }

    /**
     * 添加入职资料
     */
     public ToJson insert(HrStaffEntryWithBLOBs hrStaffEntryWithBLOBs, HttpServletRequest request){

        ToJson json = new ToJson<>(1, "err");

        List<HrStaffEntryEducation> hrStaffEntryEducationList = hrStaffEntryWithBLOBs.getHrStaffEntryEducationList();
        List<HrStaffEntryExperience> hrStaffEntryExperienceList =  hrStaffEntryWithBLOBs.getHrStaffEntryExperienceList();
        try{

            if(!StringUtils.checkNull(hrStaffEntryWithBLOBs.getAdoptPinName())){
                String adoptPinName = hrStaffEntryWithBLOBs.getAdoptPinName();
                List<Users> listByName = usersMapper.getListByName(adoptPinName);
                if(listByName.size()>0){
                    for (int i = 0; i < listByName.size(); i++) {
                        Users users = listByName.get(i);
                        if(users.getUserPriv()==6){
                            hrStaffEntryWithBLOBs.setAdoptPinId(users.getUid());
                        }
                    }
                }else{
                    json.setMsg("没有查询到该采销经理");
                    json.setFlag(1);
                    return json;
                }
                if(hrStaffEntryWithBLOBs.getAdoptPinId()==null||hrStaffEntryWithBLOBs.getAdoptPinId()<=0){
                    json.setMsg("填写的采销人员不是采销经理");
                    json.setFlag(1);
                    return json;
                }
            }else{
                json.setMsg("采销经理不能为空");
                json.setFlag(1);
                return json;
            }

            List<HrStaffEntry> hrStaffEntrys = hrStaffEntryMapper.selByCardNo(hrStaffEntryWithBLOBs.getStaffCardNo());
            for (int i = 0; i < hrStaffEntrys.size(); i++) {
                HrStaffEntry hrStaffEntry = hrStaffEntrys.get(i);
                if(hrStaffEntry!=null&&!hrStaffEntry.getAuditState().contains("8")){
                    json.setMsg("该身份证号码已经注册");
                    json.setFlag(1);
                    return json;
                }
            }


            if(!StringUtils.checkNull(hrStaffEntryWithBLOBs.getVirturalErp())){
                HrStaffEntry hrStaffEntry = hrStaffEntryMapper.selByVirturalErp(hrStaffEntryWithBLOBs.getVirturalErp());
                if(hrStaffEntry!=null){
                    json.setMsg("该虚拟ERP已经注册");
                    json.setFlag(1);
                    return json;
                }
            }




            //审核状态
            hrStaffEntryWithBLOBs.setAuditState("1");
            hrStaffEntryWithBLOBs.setLastUpdateTime(new Date());


            Integer data = hrStaffEntryMapper.insert(hrStaffEntryWithBLOBs);
            if(data != null){
                Integer id = hrStaffEntryWithBLOBs.getId();
                for (HrStaffEntryEducation hrStaffEntryEducation:hrStaffEntryEducationList) {
                    hrStaffEntryEducation.setDid(id);
                    hrStaffEntryEducationMapper.insertSelective(hrStaffEntryEducation);
                }
                for (HrStaffEntryExperience hrStaffEntryExperience:hrStaffEntryExperienceList) {
                    hrStaffEntryExperience.setDid(id);
                    hrStaffEntryExperienceMapper.insertSelective(hrStaffEntryExperience);
                }
                String attachmentStr = hrStaffEntryWithBLOBs.getAttachmentStr();
                List<HrStaffAttachmentWithBLOBs> attachments = JSONArray.parseArray(attachmentStr, HrStaffAttachmentWithBLOBs.class);

                for(int i=0,size=attachments.size();i<size;i++){
                    HrStaffAttachmentWithBLOBs hrStaffAttachmentWithBLOBs = attachments.get(i);
                    hrStaffAttachmentWithBLOBs.setStaffEntryId(hrStaffEntryWithBLOBs.getId());
                    hrStaffAttachmentMapper.insertSelective(hrStaffAttachmentWithBLOBs);
                }

                String sqlType = "xoa" + (String) request.getSession().getAttribute(
                        "loginDateSouse");

                //发送邮箱
               HrStaffEntry entry1 = hrStaffEntryMapper.selectByPrimaryKey(id);
                entry1.setAuditState("1");
                entry1.setWorkStatus("01");
                ToJson<Webmail> json11 = emailService.selectUserWebMail("admin");
                EmailBodyModel emailBodyModel = new EmailBodyModel();
                emailBodyModel.setSubject("京东驻场系统注册审核");
                emailBodyModel.setToWebmail(entry1.getStaffEmail());
                emailBodyModel.setToId2(entry1.getUserId() + ",");
                emailBodyModel.setFromWebmail(json11.getObj().get(0).getEmailUser());
                emailBodyModel.setContent("注册成功");
                /* emailService.sendEmail(emailBodyModel, new EmailModel(), sqlType, "1", request);*/
                // 发送事务提醒给申请注册采销经理
                Integer deptId = hrStaffEntryWithBLOBs.getDeptId();
                Map<String,Object> map = new HashMap<String,Object>();
                map.put("deptId",deptId);
                map.put("userPriv",6);
                map.put("uid",hrStaffEntryWithBLOBs.getAdoptPinId());
                List<Users> users2 =  usersMapper.getUserByUserPrivAnddeptId(map);
                StringBuilder toIds = new StringBuilder();
                for (int i = 0; i <users2.size() ; i++) {
                    toIds.append(users2.get(i).getUserId()).append(",");
                }
                addAffairs(entry1, entry1.getId(), toIds.toString(), request);

                json.setObject(data);
                json.setFlag(0);
                json.setMsg("true");
            }
        }catch(Exception e){
            e.printStackTrace();
            json.setObject(e);
        }

        return json;
    }

    @Override
    public HrStaffEntryWithBLOBs selectEntryById(Integer id,HttpServletRequest request) {

        HrStaffEntryWithBLOBs hrStaffEntryWithBLOBs = hrStaffEntryMapper.selectByPrimaryKey(id);
        Integer deptId = hrStaffEntryWithBLOBs.getDeptId();
        Department department = departmentMapper.selectByDeptId(deptId);
        if(department!=null&&department.getDeptName()!= null){
            hrStaffEntryWithBLOBs.setDeptmentName(department.getDeptName());
        }else{
            hrStaffEntryWithBLOBs.setDeptmentName("");
        }



        if(hrStaffEntryWithBLOBs!=null){
            //附件处理
            List<SysCode> hr_attachments = sysCodeMapper.getChildCode("HR_ATTACHMENTS");
            StringBuilder sb = new StringBuilder();
            for(SysCode sysCode:hr_attachments){
                sb.append(sysCode.getCodeNo()).append(",");
            }

            List<HrStaffAttachmentWithBLOBs> hrStaffAttachments = hrStaffAttachmentMapper.selByTypeNoEntryId(sb.toString().split(","), String.valueOf(hrStaffEntryWithBLOBs.getId()));

            if(hrStaffAttachments!=null&&hrStaffAttachments.size()>0){
                String company = (String) request.getSession().getAttribute(
                        "loginDateSouse");
                if (StringUtils.checkNull(company)){
                    List<OrgManage> org=orgManageMapper.queryId();
                    if (org!=null&&org.size()>0){
                        company = org.get(0).getOid().toString().trim();
                    }
                    company = "xoa"+company;
                }else {
                    company = "xoa" + (String) request.getSession().getAttribute(
                            "loginDateSouse");
                }

                for (int i = 0,size = hrStaffAttachments.size(); i < size; i++) {
                    HrStaffAttachmentWithBLOBs hrStaffAttachmentWithBLOBs = hrStaffAttachments.get(i);
                    if(!StringUtils.checkNull(hrStaffAttachmentWithBLOBs.getAttachmentId())){
                        Attachment attachment = attachmentMapper.findByAttachId(Integer.parseInt(hrStaffAttachmentWithBLOBs.getAttachmentId().split("@")[0]));
                        hrStaffAttachmentWithBLOBs.setAttUrl("AID=" + attachment.getAid() + "&MODULE=hr_staffinfo" + "&COMPANY=" + company + "&YM=" + attachment.getYm() + "&ATTACHMENT_ID=" + attachment.getAttachId() + "&ATTACHMENT_NAME=" + attachment.getAttachName());
                    }
                }
            }
            hrStaffEntryWithBLOBs.setAttachments(hrStaffAttachments);
        }


        return hrStaffEntryWithBLOBs;
    }

    @Override
    public ToJson importBrandAndCategory(MultipartFile file, HttpServletRequest request, HttpServletResponse response) {
         ToJson json = new ToJson();

        File dest = null;
        try {
            //判读当前系统
            //读取配置文件
            ResourceBundle rb = ResourceBundle.getBundle("upload");
            String osName = System.getProperty("os.name");
            StringBuffer stringBuffer = new StringBuffer();
            StringBuffer buffer = new StringBuffer();
            if (osName.toLowerCase().startsWith("win")) {
                //sb=sb.append(rb.getString("upload.win"));
                //判断路径是否是相对路径，如果是的话，加上运行的路径
                String uploadPath = rb.getString("upload.win");
                if (uploadPath.indexOf(":") == -1) {
                    //获取运行时的路径
                    String basePath = this.getClass().getClassLoader().getResource(".").getPath() + File.pathSeparator;
                    //获取截取后的路径
                    String str2 = "";
                    if (basePath.indexOf("/xoa") != -1) {
                        //获取字符串的长度
                        int length = basePath.length();
                        //返回指定字符在此字符串中第一次出现处的索引
                        int index = basePath.indexOf("/xoa");
                        //从指定位置开始到指定位置结束截取字符串
                        str2 = basePath.substring(0, index);
                    }
                    stringBuffer = stringBuffer.append(str2 + "/xoa");
                }
                stringBuffer.append(uploadPath);
                buffer = buffer.append(rb.getString("upload.win"));
            } else {
                stringBuffer = stringBuffer.append(rb.getString("upload.linux"));
                buffer = buffer.append(rb.getString("upload.linux"));
            }
            // 判断是否为空文件
            if (file.isEmpty()) {
                json.setMsg("请上传文件！");
                json.setFlag(1);
                return json;
            } else {
                String fileName = file.getOriginalFilename();
                if (fileName.endsWith(".xls") || fileName.endsWith(".xlsx")) {
                    String uuid = UUID.randomUUID().toString().replaceAll("-", "");
                    int pos = fileName.indexOf(".");
                    String extName = fileName.substring(pos);
                    String newFileName = uuid + extName;
                    File pathfile = new File(String.valueOf(stringBuffer));
                    if (!pathfile.exists()) {// 如果目录不存在
                        pathfile.mkdirs();// 创建文件夹
                    }
                    dest = new File(stringBuffer.toString(), newFileName);
                    file.transferTo(dest);
                    // 将文件上传成功后进行读取文件
                    // 进行读取的路径
                    String readPath = stringBuffer.append(System.getProperty("file.separator")).append(newFileName).toString();
                    InputStream in = new FileInputStream(readPath);
                    XSSFWorkbook excelObj = new XSSFWorkbook(in);
                    XSSFSheet sheetObj = excelObj.getSheetAt(0);
                    XSSFRow row;
                    int colNum = 2;
                    int lastRowNum = sheetObj.getLastRowNum();


                    SysCode category = new SysCode();
                    category.setParentNo("CATEGORY");
                    category.setCodeExt("");
                    SysCode brand;
                    for (int i = 1; i <= lastRowNum; i++) {
                        brand = new SysCode();
                        row = sheetObj.getRow(i);
                        if (row != null) {
                            for (int j = 0; j < colNum; j++) {
                                Cell cell = row.getCell(j);
                                if (cell != null) {
                                    switch (j) {
                                        case 0:
                                            if("总计".equals(cell.getStringCellValue())){
                                                break;
                                            }
                                            if(!StringUtils.checkNull(cell.getStringCellValue())){
                                                category.setCodeName(cell.getStringCellValue());
                                            }
                                            break;
                                        case 1:
                                            if(cell.getCellType()== CellType.NUMERIC){
                                                brand.setCodeName(String.valueOf(cell.getNumericCellValue()));
                                            }else{
                                                if(!StringUtils.checkNull(cell.getStringCellValue())){
                                                    brand.setCodeName(cell.getStringCellValue());
                                                }
                                            }
                                            break;

                                    }
                                }
                            }
                        }

                        // 判断是添加品类 还是添加品牌
                        if(StringUtils.checkNull(brand.getCodeName())){
                            SysCode codeNoByNameAndParentNo = sysCodeMapper.getCodeNoByNameAndParentNo(category);
                            // 判断品类是否存在
                            if(codeNoByNameAndParentNo==null){ // 不存在就进行添加
                                String s = sysCodeMapper.selectMaxNoCode("CATEGORY");
                                if(StringUtils.checkNull(s)){
                                    s = "CATEGORY0";
                                }
                                String num = s.substring(8, s.length());

                                category.setCodeNo("CATEGORY"+(Integer.valueOf(num)+1));
                                category.setCodeOrder(String.valueOf(Integer.valueOf(num)+1));

                                category.setCodeId(null);
                                sysCodeMapper.addSysChildCode(category);
                            }else{
                                category = codeNoByNameAndParentNo;
                            }

                        }else{
                            brand.setParentNo(category.getCodeNo());
                            // 判断品牌是否已经存在
                            SysCode codeNoByNameAndParentNo = sysCodeMapper.getCodeNoByNameAndParentNo(brand);
                            if(codeNoByNameAndParentNo==null){ // 不存在就进行添加
                                String s = sysCodeMapper.selectMaxNoCode(category.getCodeNo());
                                if(StringUtils.checkNull(s)){
                                    s = "0";
                                }
                                brand.setCodeNo(String.valueOf(Integer.valueOf(s)+1));
                                brand.setCodeExt("");
                                brand.setCodeOrder(String.valueOf(Integer.valueOf(s)+1));
                                sysCodeMapper.addSysChildCode(brand);
                            }
                        }

                    }

                    // 设置状态并删除文件
                    json.setFlag(0);
                    json.setMsg("ok");
                    dest.delete();
                } else {
                    json.setMsg("文件类型不正确！");
                    json.setFlag(1);
                    return json;
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
            json.setObject(e);
            json.setMsg("数据保存异常");
            json.setFlag(1);
        } finally {
            dest.delete();
        }
         return json;
    }

    @Override
    public void exportBrandAndCategory(HttpServletRequest request, HttpServletResponse response) {

         try {
             List<SysCode> list = new ArrayList<>();
             List<SysCode> category = sysCodeMapper.getChildCode("CATEGORY");
             for (int i = 0; i <category.size() ; i++) {
                 SysCode sysCode = category.get(i);
                 List<SysCode> childCode = sysCodeMapper.getChildCode(sysCode.getCodeNo());
                 for (int j = 0; j < childCode.size(); j++) {
                     SysCode sysCode1 = childCode.get(j);
                     sysCode1.setCodeName2(sysCode1.getCodeName());
                     sysCode1.setCodeName(null);
                 }
                 list.add(sysCode);
                 list.addAll(childCode);
             }

             HSSFWorkbook workbook1 = ExcelUtil.makeExcelHead("商品分类", 1);
             String[] beanProperty = {"codeName", "codeName2"};

             HSSFWorkbook workbook = ExcelUtil.exportExcelData(workbook1, list, beanProperty);
             ServletOutputStream out = response.getOutputStream();

             String filename = "品牌&品类信息表.xls";
             filename = FileUtils.encodeDownloadFilename(filename, request.getHeader("user-agent"));
             response.setContentType("application/vnd.ms-excel");
             response.setHeader("content-disposition", "attachment;filename=" + filename);
             workbook.write(out);
             out.close();
         } catch (Exception e){

         }
    }

    @Override
    public ToJson update(HrStaffEntryWithBLOBs hrStaffEntrys, HttpServletRequest request) {
         ToJson json = new ToJson();
        HrStaffEntryWithBLOBs hrStaffEntryWithBLOBs = hrStaffEntryMapper.selectByPrimaryKey(hrStaffEntrys.getId());
        try{
            if(hrStaffEntryWithBLOBs!=null){
                if(!StringUtils.checkNull(hrStaffEntrys.getAdoptPinName())&&!hrStaffEntrys.getAdoptPinName().trim().equals(hrStaffEntryWithBLOBs.getAdoptPinName())){
                    String adoptPinName = hrStaffEntrys.getAdoptPinName();
                    List<Users> listByName = usersMapper.getListByName(adoptPinName);
                    if(listByName.size()>0){
                        hrStaffEntrys.setAdoptPinId(null);
                        for (int i = 0; i < listByName.size(); i++) {
                            Users users = listByName.get(i);
                            if(users.getUserPriv()==6){
                                hrStaffEntrys.setAdoptPinId(users.getUid());
                            }
                        }
                    }else{
                        json.setMsg("没有查询到该采销经理");
                        json.setFlag(1);
                        return json;
                    }
                    if(hrStaffEntrys.getAdoptPinId()==null||hrStaffEntrys.getAdoptPinId()<=0){
                        json.setMsg("填写的采销人员不是采销经理");
                        json.setFlag(1);
                        return json;
                    }
                }

                if(!StringUtils.checkNull(hrStaffEntrys.getAttachmentStr())){
                    List<HrStaffAttachmentWithBLOBs> attachments = JSONArray.parseArray(hrStaffEntrys.getAttachmentStr(), HrStaffAttachmentWithBLOBs.class);
                    Map<String,HrStaffAttachmentWithBLOBs> attachmentMap = new HashMap<>();
                    for (int i = 0; i < attachments.size(); i++) {
                        HrStaffAttachmentWithBLOBs hrStaffAttachmentWithBLOBs = attachments.get(i);
                        hrStaffAttachmentWithBLOBs.setStaffEntryId(hrStaffEntrys.getId());
                        attachmentMap.put(hrStaffAttachmentWithBLOBs.getAttachmentTypeNo(),hrStaffAttachmentWithBLOBs);
                    }
                    //附件处理
                    List<SysCode> hr_attachments = sysCodeMapper.getChildCode("HR_ATTACHMENTS");
                    StringBuilder sb = new StringBuilder();
                    for(SysCode sysCode:hr_attachments){
                        sb.append(sysCode.getCodeNo()).append(",");
                    }

                    List<HrStaffAttachmentWithBLOBs> hrStaffAttachments = hrStaffAttachmentMapper.selByTypeNoEntryId(sb.toString().split(","), String.valueOf(hrStaffEntryWithBLOBs.getId()));
                    if(attachments.size()>=hrStaffAttachments.size()){
                        for (int i = 0; i < hrStaffAttachments.size(); i++) {
                            HrStaffAttachmentWithBLOBs hrStaffAttachmentWithBLOBs = hrStaffAttachments.get(i);
                            HrStaffAttachmentWithBLOBs newAttachment = attachmentMap.get(hrStaffAttachmentWithBLOBs.getAttachmentTypeNo());
                            if(newAttachment!=null
                                    &&!StringUtils.checkNull(newAttachment.getAttachmentId())
                                    &&!newAttachment.getAttachmentId().equals(hrStaffAttachmentWithBLOBs.getAttachmentId())){
                                // 设置id
                                newAttachment.setStaffEntryId(hrStaffAttachmentWithBLOBs.getStaffEntryId());
                                // 创建条件类
                                HrStaffAttachmentExample example = new HrStaffAttachmentExample();
                                HrStaffAttachmentExample.Criteria criteria = example.createCriteria();
                                // 设置条件
                                criteria.andStaffEntryIdEqualTo(newAttachment.getStaffEntryId());
                                criteria.andAttachmentTypeNoEqualTo(newAttachment.getAttachmentTypeNo());
                                // 执行更新
                                hrStaffAttachmentMapper.updateByExampleSelective(newAttachment,example);
                            }
                            attachmentMap.remove(hrStaffAttachmentWithBLOBs.getAttachmentTypeNo());
                        }
                        // 判断如果map中还有剩余 进行保存
                        if(attachmentMap.size()>0){
                            for(Map.Entry<String, HrStaffAttachmentWithBLOBs> entry : attachmentMap.entrySet()){
                                HrStaffAttachmentWithBLOBs value = entry.getValue();
                                hrStaffAttachmentMapper.insertSelective(value);
                            }
                        }
                    }

                }

                hrStaffEntryMapper.updateByPrimaryKeySelective(hrStaffEntrys);
            }
        }catch (Exception e){
            e.printStackTrace();
            json.setObject(e);
            return json;
        }

        json.setFlag(0);
        json.setMsg("ok");
        return json;
    }

    public String getEncryptString(String password) {

        String md5WithSalt = null;
        //非空字符串
        if (password != null && !"".equals(password.trim())) {
            md5WithSalt = MD5Utils.md5Crypt(password.trim().getBytes(), "$1$".concat(EncryptSalt.getRandomSalt(12)));
        }
        //“”字符串加密要得到tVHbkPWW57Hw.作为加密后结果
        if (password != null && "".equals(password.trim())) {
            md5WithSalt = "tVHbkPWW57Hw.";
        }
        return md5WithSalt;
    }
    @Async
    public void addAffairs(final HrStaffEntry hrStaffEntry, final Integer id, final String todoId, HttpServletRequest request) {
        final String sqlType = (String) request.getSession().getAttribute("loginDateSouse");
        Cookie redisSessionCookie = CookiesUtil.getCookieByName(request, "redisSessionId");

        final Users nowUser = SessionUtils.getSessionInfo(request.getSession(), Users.class, new Users(),redisSessionCookie);

        this.threadPoolTaskExecutor.execute(new Runnable() {
            @Override
            public void run() {
                if(!StringUtils.checkNull(sqlType)){
                    ContextHolder.setConsumerType("xoa" + sqlType);
                }else{
                    List<OrgManage> orgManage = orgManageMapper.getOrgManage();
                    for (int i = 0; i < orgManage.size(); i++) {
                        OrgManage orgManage1 = orgManage.get(i);
                        if("1".equals(orgManage1.getIsOrg())){
                            ContextHolder.setConsumerType("xoa"+orgManage1.getOid());
                        }
                    }

                }
                SmsBody smsBody = new SmsBody();
                smsBody.setContent("请查收需要审核的注册信息");
                SysCode sysCode = new SysCode();
                sysCode.setCodeName("注册审批");
                sysCode.setParentNo("SMS_REMIND");
                if (sysCodeMapper.getCodeNoByNameAndParentNo(sysCode) != null) {
                    smsBody.setSmsType(sysCodeMapper.getCodeNoByNameAndParentNo(sysCode).getCodeNo());
                }
                smsBody.setRemindUrl("/personManagement/newIntroduction?id=" + id);
                String title =  "：请审核注册信息！";
                String context = "注册审批" ;
                // 判断当前是否是注册事物提醒 如果不是的话 发送人为当前登陆人 如果是注册的话 发送人为admin
                if(nowUser!=null&&!StringUtils.checkNull(nowUser.getUserId())){
                    smsBody.setFromId(nowUser.getUserId());
                } else{
                    smsBody.setFromId("admin");
                }
                smsBody.setSendTime(DateFormat.getTime(DateFormat.getStrDate(new Date())));
                smsService.saveSms(smsBody, todoId, "1", "1", title, context, sqlType);
            }
        });

    }

    //判断对象是否为null或空字符串
    private static boolean notNullOrEmpty(Object object) {
        try {
            return null != object && !"".equals(object.toString().trim());
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
    }
}
